package com.aier.user.service.impl;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;

import com.aier.common.dao.ICommonDAO;
import com.aier.user.entity.User;
import com.aier.user.entity.mapper.UserRowMapper;
import com.aier.user.entity.mapper.VisitorRecordRowMapper;
import com.aier.user.service.IFindPeopleService;

@Component("findPeopleService")
public class FindPeopleServiceImpl_J implements IFindPeopleService{
    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }

    @Override
    public Map<String, Object> searchPeaople(int page, int page_size, Long sid,
             Long cid,String sex,String nickname) {
        
        List<Object> largcount = new ArrayList<Object>();
        String sqlp = "select u.*, (select count(*) from follow where uid = u.uid) as cfollow, " +
                "(select count(*) from follow where fuid = u.uid) as cfans, " +
                "(select count(*) from fresh where uid = u.uid ) as cfresh from user u";
        String sqlt = "select u.*, (select count(*) from follow where uid = u.uid) as cfollow, " +
                "(select count(*) from follow where fuid = u.uid) as cfans, " +
                "(select count(*) from fresh where uid = u.uid ) as cfresh from user u";
        if (cid != -1 && cid != null) {
            sqlp += " left join class_parent cp on cp.uid = u.uid where cp.cid = ?";
            sqlt += " left join school_teacher st on st.uid = u.uid left join class_teacher ct on ct.tid = st.tid where ct.cid = ?";
            largcount.add(cid);
        } else {
            if (sid != -1 && cid != null) {
                sqlp += " left join class_parent cp on cp.uid = u.uid left join classes c on c.cid = cp.cid where c.sid = ?";
                sqlt += " left join school_teacher st on st.uid = u.uid where st.sid = ?";
                largcount.add(sid);
            }
        }
        
        if (nickname != null && !"".equals(nickname)) {
            if ((cid == -1||cid == null) && (sid ==-1||sid == null)) {
                sqlp += " where u.nickname like ?";
                sqlt += " where u.nickname like ?";
            } else {
                sqlp += " and u.nickname like ?";
                sqlt += " and u.nickname like ?";
            }
            
            largcount.add("%" + nickname + "%");
            if (!"-1".equals(sex)) {
                sqlp += " and u.sex = ?";
                sqlt += " and u.sex = ?";
                largcount.add(sex);
            } 
            sqlp += " and u.isbaby = 0";
            sqlt += " and u.isbaby = 0";
        } else {
            if (null != sex && !"-1".equals(sex)) {
                if ((cid == -1||cid == null) && (sid ==-1||sid == null)) {
                    sqlp += " where u.sex = ?";
                    sqlt += " where u.sex = ?";
                } else {
                    sqlp += " and u.sex = ?";
                    sqlt += " and u.sex = ?";
                }
                
                largcount.add(sex);
            } else {
                if ((cid == -1||cid == null) && (sid ==-1||sid == null)) {
                    sqlp += " where u.isbaby = 0";
                    sqlt += " where u.isbaby = 0";
                } else {
                    sqlp += " and u.isbaby = 0";
                    sqlt += " and u.isbaby = 0";
                }
            }
        }
        
        if (sqlp.equals(sqlt)) {
            return commonDao.queryPage(sqlp, page, page_size, largcount, new UserRowMapper(UserRowMapper.BRIEF));
        } else {
            List<Object> lcount = new ArrayList<Object>();
            for (int i = 0;i<largcount.size();i++) {
                lcount.add(largcount.get(i));
            }
            for (int i = 0;i<largcount.size();i++) {
                lcount.add(largcount.get(i));
            }
            
            return commonDao.queryPage(sqlp+" union "+sqlt, page, page_size, lcount, new UserRowMapper(UserRowMapper.BRIEF));
        }
        
    }

    @SuppressWarnings("unchecked")
	@Override
    public List<User> searchInterestPeaople(Long uid) {
    	String sql = "select * from ("+
        		"select u.*,0 as et,0 as ep,0 as es,0 as fcount from user u where u.uid = 1807 " +
        		"and u.uid not in (select fuid from follow where uid = ?) and u.uid != ? union " +
        		"select u.*, " +
        		"(case when ct.cid in (select cid from class_teacher ct left join school_teacher st on ct.tid = st.tid where uid = ?) then 0 else 1 end) as et, " +
        		"(case when cp.cid in (select distinct (cid) from class_parent cp where cp.uid = ?) then 0 else 1 end) as ep," +
        		"(case when st.sid in (select sid from school_teacher where uid = ?) then 0 else 1 end) as es," +
        		"(select count(*) from follow where uid = u.uid) as fcount " +
        		"from user u " +
        		"left join class_parent cp on cp.uid = u.uid " +
        		"left join school_teacher st on st.uid = u.uid left join class_teacher ct on ct.tid = st.tid " +
        		"where u.isbaby = 0 and u.uid not in (select fuid from follow where uid = ?) and u.uid != ? " +
        		" group by u.uid order by et, ep, es,fcount desc) as total order by rand()  limit 10";
        return (List<User>) commonDao.list(sql, new Object[]{uid,uid,uid,uid,uid,uid,uid}, new UserRowMapper(UserRowMapper.BABY_CLASSNAME));
        
    }

    @Override
    public int insertVisitor(Long vuid, Long buid) {
        
        if (commonDao.list("select * from visitor_record where vuid = ? and buid = ?",
                new Object[]{vuid,buid},new VisitorRecordRowMapper()).size() == 0) {
            return commonDao.update("insert into visitor_record(vuid,buid,cdate) values (?,?,?)",
                    new Object[]{vuid,buid,new Timestamp(new Date().getTime())});
        } else {
            return commonDao.update("update visitor_record set cdate = ? where vuid = ? and buid = ?",
                    new Object[]{new Timestamp(new Date().getTime()),vuid,buid});
        }
       
    }

    @Override
    public Map<String, Object> searchVisitor(int page, int page_size, Long uid) {
        return commonDao.queryPage("select * from user u left join visitor_record vr on u.uid = vr.vuid where vr.buid = ? order by vr.cdate desc", 
                page, page_size, new Object[]{uid}, new UserRowMapper());
    }

    @Override
    public User searchInfoDetail(Long uid, Long buid) {
        String sql = "select u.*, ( select count(*) from follow where uid = u.uid ) as cfollow, ( select count(*) from follow where fuid = u.uid )" +
        		" as cfans, ( select count(*) from fresh where uid = u.uid ) as cfresh from user u where u.uid = ?";
        
        User user = (User) commonDao.listByObject(sql, new Object[]{buid},new UserRowMapper(UserRowMapper.BRIEF));
        // 查看是否宝宝同班
        @SuppressWarnings("unchecked")
        List<String> uidList = (List<String>)commonDao.list("select uid from class_parent cp where cid in " +
        		"(select cid from class_parent cp where cp.uid = ?)", 
                new Object[]{uid},new RowMapper<String>() {
                    @Override
                    public String mapRow(ResultSet rs, int arg1)
                        throws SQLException {
                        return rs.getString("uid");
                    }
                }
        );
        if (uidList.contains(buid.toString())) {
            user.setRelation("宝宝同班");
        } else {
            // 查看是否宝宝同校
            @SuppressWarnings("unchecked")
            List<String> sidList = (List<String>)commonDao.list("select sid from classes c left join class_parent cp on c.cid = cp.cid " +
            		"where cp.uid = ?", 
                    new Object[]{uid},new RowMapper<String>() {
                        @Override
                        public String mapRow(ResultSet rs, int arg1)
                            throws SQLException {
                            return rs.getString("sid");
                        }
                    }
            );
            @SuppressWarnings("unchecked")
            List<String> sidList1 = (List<String>)commonDao.list("select sid from classes c left join class_parent cp on c.cid = cp.cid " +
                    "where cp.uid = ?", 
                    new Object[]{buid},new RowMapper<String>() {
                        @Override
                        public String mapRow(ResultSet rs, int arg1)
                            throws SQLException {
                            return rs.getString("sid");
                        }
                    }
            );
            for (String sid:sidList) {
                if (sidList1.contains(sid)) {
                    user.setRelation("宝宝同校");
                    break;
                }
            }
        }
        // 共同关注
        @SuppressWarnings("unchecked")
        List<User> uList = (List<User>) commonDao.list("select * from user u left join follow f on u.uid = f.fuid where f.uid = ?",
                new Object[]{uid},new UserRowMapper());
        @SuppressWarnings("unchecked")
        List<User> buList = (List<User>) commonDao.list("select * from user u left join follow f on u.uid = f.fuid where f.uid = ?",
                new Object[]{buid},new UserRowMapper());
        List<User> userList = new ArrayList<User>();
        for (User u : uList) {
            for (User bu:buList) {
                if (bu.getUid().equals(u.getUid())) {
                    userList.add(u);
                    break;
                }
            }
        }
        user.setCommonFollow(userList);
        return user;
    }

    @Override
    public User searchSelfInfo(Long uid) {
        return (User) commonDao.listByObject("select u.*,(select count(*) from follow where uid = u.uid) as cfollow, " +
                "(select count(*) from follow where fuid = u.uid) as cfans, " +
                "(select count(*) from fresh where uid = u.uid ) as cfresh from user u where u.uid = ?", 
                new Object[]{uid},new UserRowMapper(UserRowMapper.BRIEF));
    }
    
    @Override
    public Map<String, Object> searchPeaople(int page, int page_size, String nickname,Long Uid) {
        
        List<Object> largcount = new ArrayList<Object>();
        String sql = "select u.*, (select count(*) from follow where uid = u.uid) as cfollow, " +
                "(select count(*) from follow where fuid = u.uid) as cfans from user u " +
                " left join follow f on f.fuid=u.uid " +
                "where u.isbaby = 0 and  f.uid != ?";
        
        if (nickname != null && !"".equals(nickname)) {
            sql += " and u.nickname like ? or u.account like ?";
            
            largcount.add(Uid);
            largcount.add("%" + nickname + "%");
            largcount.add("%" + nickname + "%");
        }
        
        sql += " order by cfollow desc,cfans desc";
        
        return commonDao.queryPage(sql, page, page_size, largcount, new UserRowMapper());
        
    }

}
